package com.wuseguang.springcloud.service.impl;

import com.wuseguang.springcloud.dao.jdbc.StudentEvaluationScoreRecordJdbc;
import com.wuseguang.springcloud.dao.repository.StudentEvaluationScoreRecordRepository;
import com.wuseguang.springcloud.dao.repository.TextEvaluationRepositoty;
import com.wuseguang.springcloud.service.StudentEvaluationScoreRecordService;

import com.wuseguang.springcloud.util.Result;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 学生评价分数记录 表 服务实现类
 * </p>
 *
 * @author
 * @since 2020-04-22
 */
@Service
public class StudentEvaluationScoreRecordServiceImpl implements StudentEvaluationScoreRecordService {

    @Autowired
    private StudentEvaluationScoreRecordJdbc studentEvaluationScoreRecordJdbc;
    @Autowired
    private StudentEvaluationScoreRecordRepository studentEvaluationScoreRecordRepository;
    @Autowired
    private TextEvaluationRepositoty textEvaluationRepositoty;

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Override
    public Result findClassPerformance(Integer classId, String teacherId, Integer type, Integer findType, String startTime, String endTime) {

        logger.info("查询班级表现");

        //如果查询等于自定义,并且开始时间结束时间有一个为空就不查询
        if (findType == 3 && StringUtils.isEmpty(startTime)
                || findType == 3 && StringUtils.isEmpty(endTime)) {
            return new Result();
        }

        //查询班级表现详情sql
        String sql = " from (" +
                " select sesr.id,sesr.fraction,sesr.content,sesr.name,CONCAT('由',t.`name`,'老师点评了',s.student_name,'同学') commentDetails,sesr.appraiser_id,DATE_FORMAT(sesr.add_time,'%Y-%m-%d %T') add_time,si.icon  from student_evaluation_score_record sesr LEFT JOIN teacher t on(t.id=sesr.appraiser_id) LEFT JOIN  student s on(sesr.type_id=s.id) left join score_item si on(si.id=sesr.score_item_id) where sesr.type = 0 and s.class_id=? and sesr.appraiser=0 " +
                " UNION ALL " +
                " select sesr.id,sesr.fraction,sesr.content,sesr.name,CONCAT('由',t.`name`,'老师点评了',cg.name) commentDetails,sesr.appraiser_id,DATE_FORMAT(sesr.add_time,'%Y-%m-%d %T') add_time,si.icon from student_evaluation_score_record sesr LEFT JOIN teacher t on(t.id=sesr.appraiser_id) LEFT JOIN class_group cg on(sesr.type_id=cg.id) left join score_item si on(si.id=sesr.score_item_id) where sesr.type = 2 and cg.class_id=? and sesr.appraiser=0 " +
                " UNION ALL " +
                " select sesr.id,sesr.fraction,sesr.content,sesr.name,CONCAT('由',t.`name`,'老师点评了全班') commentDetails,sesr.appraiser_id,DATE_FORMAT(sesr.add_time,'%Y-%m-%d %T') add_time,si.icon  from student_evaluation_score_record sesr LEFT JOIN teacher t on(t.id=sesr.appraiser_id) left join score_item si on(si.id=sesr.score_item_id)  where sesr.type = 1 and sesr.type_id =? and sesr.appraiser=0 " +
                " ) a where 0 = 0 ";

        //查询文字点评数据
        String sqlThree = "select te.id, te.content, te.class_id, te.teacher_id, te.teacher_name, te.student_id, DATE_FORMAT( add_time, '%Y-%m-%d %T' ) add_time, CONCAT( 'https://oss.yuguozhijia.online/systemicon/%E6%96%87%E5%AD%97%E7%82%B9%E8%AF%84.png' ) icon ,s.student_name " +
                "text_evaluation  te left join student s on(s.id = te.student_id) WHERE te.class_id = ? ";

        //判断是否只查询自己的
        if (type == 1) {
            logger.info("班级表现只查询自己评价的");
            sql += " and appraiser_id = " + teacherId;
            sqlThree += " and teacher_id = " + teacherId;
        }
        //判断查询类型 0本周,1上周,2本月,3自定义
        if (findType == 1) {
            logger.info("查询上周数据");
            sql += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now())-1 ";
            sqlThree += " and YEARWEEK(date_format(add_time,'%Y-%m-d')) = YEARWEEK(now())-1 ";
        } else if (findType == 2) {
            logger.info("查询本月数据");
            sql += " and date_format(add_time,'%Y-%m')=date_format(now(),'%Y-%m') ";
            sqlThree += " and date_format(add_time,'%Y-%m')=date_format(now(),'%Y-%m') ";
        } else if (findType == 3) {
            logger.info("查询自定义数据");
            sql += " and date_format(add_time,'%Y-%m-%d')  between date_format('" + startTime + "','%Y-%m-%d') and date_format(" + endTime + ",'%Y-%m-%d') ";
            sqlThree += " and date_format(add_time,'%Y-%m-%d') between date_format('" + startTime + "','%Y-%m-%d') and date_format(" + endTime + ",'%Y-%m-%d') ";
        } else {
            logger.info("查询本周数据");
            sql += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now()) ";
            sqlThree += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now()) ";
        }

        //重新拼接sql
        String sqlOne = "select * " + sql +" order by id desc ";
        //查询得分情况
        String sqlTwo = "select sum(fraction) fractionSum  " + sql + " GROUP BY sign(fraction) ORDER BY fractionSum desc";

         sqlThree  += " order by id desc ";

        //查班级表现详情
        List<Map<String, Object>> list = studentEvaluationScoreRecordJdbc.queryListMap(sqlOne, classId, classId, classId);
        //查询加减分
        List<Map<String, Object>> fractionList = studentEvaluationScoreRecordJdbc.queryListMap(sqlTwo, classId, classId, classId);
        //查询文字点评
        List<Map<String, Object>> textList = studentEvaluationScoreRecordJdbc.queryListMap(sqlThree, classId);

        if(fractionList.isEmpty()){
            //应为这个学生没有分数，所以要填写默认分数
            Map<String,Object> map1 = new HashMap<>();
            map1.put("fractionSum",0);
            Map<String,Object> map2 = new HashMap<>();
            map2.put("fractionSum",0);
            fractionList.add(map1);
            fractionList.add(map2);
        }else if(fractionList.size()==1){
            //判断正负数
            if(Integer.valueOf(fractionList.get(0).get("fractionSum").toString())>0){
                Map<String,Object> map2 = new HashMap<>();
                map2.put("fractionSum",0);
                fractionList.add(map2);
            }else{
                //获取负分的分数
                Integer  fractionSum =  Integer.valueOf(fractionList.get(0).get("fractionSum").toString());
                //重新创建
                fractionList = new ArrayList<>();
                Map<String,Object> map1 = new HashMap<>();
                map1.put("fractionSum",0);
                Map<String,Object> map2 = new HashMap<>();
                map2.put("fractionSum",fractionSum);
                //赋值
                fractionList.add(map1);
                fractionList.add(map2);
            }
        }

        //返回结果
        Map<String, Object> map = new HashMap<>();
        map.put("list", list);
        map.put("fractionList", fractionList);
        map.put("textList", textList);
        return new Result(map);
    }

    @Override
    public Result deltePerformanceScore(Integer id) {
        //先删除学生分数再删除分数记录
        try {
            String sql = "DELETE FROM `student_evaluation_score` WHERE `score_record_id` = ?";
            studentEvaluationScoreRecordJdbc.updateByParameter(sql, id);
            studentEvaluationScoreRecordRepository.deleteById(id);
        } catch (Exception e) {

        }
        return new Result();
    }

    @Override
    public Result findPersonalPerformance(Integer studentId, Integer findType, String startTime, String endTime) {

        //如果查询等于自定义,并且开始时间结束时间有一个为空就不查询
        if (findType == 3 && StringUtils.isEmpty(startTime)
                || findType == 3 && StringUtils.isEmpty(endTime)) {
            return new Result();
        }

        String sql = " from ( " +
                " select sesr.id,sesr.fraction,sesr.content,sesr.name,CONCAT('由',t.`name`,'老师点评了',s.student_name,'同学') commentTime,sesr.appraiser_id,DATE_FORMAT(sesr.add_time,'%Y-%m-%d %T') add_time,si.icon from student_evaluation_score_record sesr LEFT JOIN teacher t on(t.id=sesr.appraiser_id) LEFT JOIN  student s on(sesr.type_id=s.id) left join score_item si on(si.id=sesr.score_item_id) where sesr.type = 0 and s.id=? and sesr.appraiser=0 " +
                " UNION ALL " +
                " select sesr.id,sesr.fraction,sesr.content,sesr.name,CONCAT('由',t.`name`,'老师点评了',IFNULL(cg.name,'小组')) commentTime,sesr.appraiser_id,DATE_FORMAT(sesr.add_time,'%Y-%m-%d %T') add_time ,si.icon from student_evaluation_score d,student_evaluation_score_record sesr LEFT JOIN teacher t on(t.id=sesr.appraiser_id) LEFT JOIN group_student gs on(sesr.type_id=gs.group_id) LEFT JOIN class_group cg on(gs.group_id=cg.id) left join score_item si on(si.id=sesr.score_item_id)  where d.score_record_id=sesr.id AND sesr.type = 2 and d.student_id=? and sesr.appraiser=0 GROUP BY sesr.id" +
                " UNION ALL " +
                " select sesr.id,sesr.fraction,sesr.content,sesr.name,CONCAT('由',t.`name`,'老师点评了全班') commentTime,sesr.appraiser_id,DATE_FORMAT(sesr.add_time,'%Y-%m-%d %T') add_time,si.icon from student_evaluation_score_record sesr LEFT JOIN teacher t on(t.id=sesr.appraiser_id)  LEFT JOIN  student s on(sesr.type_id=s.class_id) left join score_item si on(si.id=sesr.score_item_id)  where sesr.type = 1 and s.id=? and sesr.appraiser=0 " +
                ") a where 0 = 0 ";


        //查询文字点评数据
        String sqlThree = "select id,content,class_id,teacher_id,teacher_name,student_id,DATE_FORMAT(add_time,'%Y-%m-%d %T')  add_time,CONCAT('https://oss.yuguozhijia.online/systemicon/%E6%96%87%E5%AD%97%E7%82%B9%E8%AF%84.png') icon from text_evaluation where student_id = ? ";

        //判断查询类型
        if (findType == 1) {
            logger.info("查询上周数据");
            sql += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now())-1 ";
            sqlThree += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now())-1 ";
        } else if (findType == 2) {
            logger.info("查询本月数据");
            sql += " and date_format(add_time,'%Y-%m')=date_format(now(),'%Y-%m') ";
            sqlThree += " and date_format(add_time,'%Y-%m')=date_format(now(),'%Y-%m') ";
        } else if (findType == 3) {
            logger.info("查询自定义数据");
            sql += " and date_format(add_time,'%Y-%m-%d')  between date_format('" + startTime + "','%Y-%m-%d') and date_format('" + endTime + "','%Y-%m-%d') ";
            sqlThree += " and date_format(add_time,'%Y-%m-%d')  between date_format('" + startTime + "','%Y-%m-%d') and date_format('" + endTime + "','%Y-%m-%d') ";
        } else {
            logger.info("查询本周数据");
            sql += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now()) ";
            sqlThree += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now()) ";
        }

        //重新拼接sql
        String sqlOne = "select * " + sql+" order by id desc ";
        //查询得分情况
        String sqlTwo = "select sum(fraction) fractionSum  " + sql + " GROUP BY sign(fraction) ORDER BY fractionSum desc";

        sqlThree  += " order by id desc ";

        //查班级表现详情
        List<Map<String, Object>> list = studentEvaluationScoreRecordJdbc.queryListMap(sqlOne, studentId, studentId, studentId);
        //查询加减分
        List<Map<String, Object>> fractionList = studentEvaluationScoreRecordJdbc.queryListMap(sqlTwo, studentId, studentId, studentId);
        //查询文字点评
        List<Map<String, Object>> textList = studentEvaluationScoreRecordJdbc.queryListMap(sqlThree, studentId);

        if(fractionList.isEmpty()){
            //应为这个学生没有分数，所以要填写默认分数
            Map<String,Object> map1 = new HashMap<>();
            map1.put("fractionSum",0);
            Map<String,Object> map2 = new HashMap<>();
            map2.put("fractionSum",0);
            fractionList.add(map1);
            fractionList.add(map2);
        }else if(fractionList.size()==1){
            //判断正负数
            if(Integer.valueOf(fractionList.get(0).get("fractionSum").toString())>0){
                Map<String,Object> map2 = new HashMap<>();
                map2.put("fractionSum",0);
                fractionList.add(map2);
            }else{
                //获取负分的分数
                Integer  fractionSum =  Integer.valueOf(fractionList.get(0).get("fractionSum").toString());
                //重新创建
                fractionList = new ArrayList<>();
                Map<String,Object> map1 = new HashMap<>();
                map1.put("fractionSum",0);
                Map<String,Object> map2 = new HashMap<>();
                map2.put("fractionSum",fractionSum);
                //赋值
                fractionList.add(map1);
                fractionList.add(map2);
            }
        }

        //返回结果
        Map<String, Object> map = new HashMap<>();
        map.put("list", list);
        map.put("fractionList", fractionList);
        map.put("textList", textList);
        return new Result(map);
    }

    @Override
    public Result deleteTextEvaluation(Integer id) {
        try {
            textEvaluationRepositoty.deleteById(id);
        } catch (Exception e) {

        }
        return new Result();
    }

    @Override
    public Result findTeacherScoringStatistics(String schoolId, Integer teacherId,Integer findType, String startTime, String endTime) {

        logger.info("查询");



        String time = "";

        //判断查询类型
        if (findType == 1) {
            logger.info("查询上周数据");
            time += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now())-1 ";
        } else if (findType == 2) {
            logger.info("查询本月数据");
            time += " and date_format(add_time,'%Y-%m')=date_format(now(),'%Y-%m') ";
        } else if (findType == 3) {
            logger.info("查询自定义数据");
            time += " and date_format(add_time,'%Y-%m-%d')  between date_format('" + startTime + "','%Y-%m-%d') and date_format('" + endTime + "','%Y-%m-%d') ";
        } else {
            logger.info("查询本周数据");
            time += " and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now()) ";
        }

        //查询教师评分统计
        String sqlOne = "SELECT COUNT(1) praiseCount, 0 toBePraisedCount, 0 textCount, t.id " +
                " FROM student_evaluation_score ses LEFT JOIN teacher t ON ( t.id = ses.appraiser_id ) " +
                "LEFT JOIN score_item si ON ( si.id = ses.score_item_id )  " +
                " WHERE t.school_id = ?  AND si.type =? AND ses.appraiser = 0 "+time+" GROUP BY t.id";


        String sqlTwo = "SELECT 0 praiseCount, COUNT(1) toBePraisedCount, 0 textCount, t.id " +
                " FROM student_evaluation_score ses LEFT JOIN teacher t ON ( t.id = ses.appraiser_id ) " +
                "LEFT JOIN score_item si ON ( si.id = ses.score_item_id )  " +
                " WHERE t.school_id = ?  AND si.type =? AND ses.appraiser = 0 "+time+" GROUP BY t.id";

        //查询教师文字点评统计
        String sqlThree ="select 0 praiseCount, 0 toBePraisedCount, COUNT(1) textCount, t.id from text_evaluation te " +
                "left join teacher t  on(t.id=te.teacher_id) where t.school_id =? "+time+" GROUP BY t.id";


        String sql = "select t.name teacherName,if(sum( praiseCount ) is null ,0,sum( praiseCount )) praiseCount," +
                " if(sum( toBePraisedCount ) is null ,0,sum( toBePraisedCount )) toBePraisedCount," +
                " if(sum( textCount ) is null,0,sum( textCount )) textCount  from teacher t LEFT JOIN (" +
                " ("+sqlOne+") UNION ALL (" +sqlTwo+")  UNION ALL (" +sqlThree+")"+
                ") a on(t.id=a.id) where t.school_id = ? GROUP BY t.id";


        List<Map<String,Object>> list = studentEvaluationScoreRecordJdbc.queryListMap(sql,schoolId,0,schoolId,1,schoolId,schoolId);
        return new Result(list);
    }
}
